﻿using Reactive.Bindings;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.SQLite;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using WPF_ReactiveProperty_PathSearchUtil.Models;
using WPF_ReactiveProperty_PathSearchUtil.Service;
using WPF_ReactiveProperty_PathSearchUtil.Util;

namespace WPF_ReactiveProperty_PathSearchUtil.Data_Access
{
    public class LocalSQLite : CSVReadOrWrite
    {
        /// <summary>
        /// 根据文件路径读取文件内容
        /// </summary>
        /// <param name="items"> imPath：导入文件路径</param>
        /// <returns>成功：返回list，失败：返回 字符串</returns>
        public Resp Read(params object[] items)
        {
            String imPath = null;
            ReactiveCollection<FileInfoModel> fileList = new ReactiveCollection<FileInfoModel>();
            foreach (object item in items)
            {
                if (item is string)
                {
                    imPath = (string)item;
                }
            }
            if (!File.Exists(imPath))
            {
                return Resp.Fail("文件不存在。");
            }
            String connstr = "Data source=" + imPath + ";version=3;";
            //try
            //{
                using (var conn = new SQLiteConnection(connstr))
                {
                    conn.Open();
                    var select = "SELECT fileName,filePath,fileType,fileSize,fileDate from FileInfos";
                    using (var sc = new SQLiteCommand(select, conn))
                    {
                        var sr = sc.ExecuteReader();
                        while (sr.Read())
                        {
                            
                            FileInfoModel model = new FileInfoModel();
                            model.FileName.Value = sr.GetString(0);
                            model.FilePath.Value = sr.GetString(1);
                            model.FileType.Value = sr.GetString(2);
                            model.FileSize.Value = sr.GetDouble(3);
                            model.FileDate.Value = sr.GetDateTime(4);
                            fileList.Add(model);
                        }
                    }
                }
            //}
            //catch(Exception e)
            //{
            //    Console.WriteLine(e.Message);
            //    return Resp.Fail("数据格式有误。");
            //}
            return Resp.Success(fileList);
        }

        /// <summary>
        /// 根据目录路径，保存db文件
        /// </summary>
        /// <param name="items">exPath：导出的目录，fileList：导出的列表</param>
        /// <returns>成功：返回文件路径，失败：返回字符串</returns>
        public Resp Write(params object[] items)
        {
            String exPath = null;
            ReactiveCollection<FileInfoModel> fileList = null;
            foreach (object item in items)
            {
                if (item is string)
                {
                    exPath = (string)item;
                    continue;
                }
                if (item is ReactiveCollection<FileInfoModel>)
                {
                    fileList = (ReactiveCollection<FileInfoModel>)item;
                    continue;
                }
            }
            if (!Directory.Exists(exPath))
            {
                try
                {
                    Directory.CreateDirectory(exPath);
                }
                catch
                {
                    return Resp.Fail("导出目录不存在。");
                }
            }
            if (exPath.LastIndexOf("\\") != exPath.Length - 1)
            {
                exPath += "\\";
            }
            String filePath = exPath + System.DateTime.Now.ToString().Replace(":", "-").Replace("/", "-") + " CSVResult.db";
            //String filePath = exPath + "Result.db";
            String connstr = "Data source=" + filePath;
            using (var conn = new SQLiteConnection(connstr))
            {
                conn.Open();
                var createTable = @"CREATE TABLE IF NOT EXISTS FileInfos(
                                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                                        fileName TEXT,
                                        filePath TEXT,
                                        fileType TEXT,
                                        fileSize DOUBLE,
                                        fileDate DATETIME)";
                using (var ctc = new SQLiteCommand(createTable, conn))
                {
                    ctc.ExecuteNonQuery();
                }
                var clear = "delete from FileInfos";
                using (var cc = new SQLiteCommand(clear, conn))
                {
                    cc.ExecuteNonQuery();
                }
                foreach (var fileInfo in fileList)
                {

                    var insert = @"INSERT INTO FileInfos(fileName,filePath,fileType,fileSize,fileDate)
                                                VALUES(@Name,@Path,@Type,@Size,@Date)";
                    using (var ic = new SQLiteCommand(insert, conn))
                    {
                        ic.Parameters.AddWithValue("@Name", fileInfo.FileName.Value);
                        ic.Parameters.AddWithValue("@Path", fileInfo.FilePath.Value);
                        ic.Parameters.AddWithValue("@Type", fileInfo.FileType.Value);
                        ic.Parameters.AddWithValue("@Size", fileInfo.FileSize.Value);
                        ic.Parameters.AddWithValue("@Date", fileInfo.FileDate.Value);
                        ic.ExecuteNonQuery();
                    }
                }
            }
            return Resp.Success(filePath);
        }
    }
}
